package com.bitcoin.scheduler.repository;

import com.bitcoin.scheduler.domain.BccPriceInfoCrawl;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

import static com.bitcoin.scheduler.domain.Bcc.BathType.CRAWL_BASIC_INFO;

public interface BccPriceInfoCrawlDao extends JpaRepository<BccPriceInfoCrawl, String>, JpaSpecificationExecutor<BccPriceInfoCrawl> {

    /**
     * 查询最新价格信息
     *
     * @return 查询最新价格信息
     */
    @Query(nativeQuery = true, value = "" +
        "SELECT crawl.* " +
        "FROM bcc_price_info_crawl crawl " +
        "       INNER JOIN (SELECT batch_no " +
        "                   FROM bcc_batch batch " +
        "                          INNER JOIN (SELECT batch_time_end date " +
        "                                      FROM bcc_batch batch " +
        "                                      WHERE batch.batch_type = 'crawl_price_info' " +
        "                                        AND ABS(TIMESTAMPDIFF(MINUTE, batch_time_end, :selectedDate)) <= :minuteUnit " +
        "                                      ORDER BY batch_time_end) t on t.date = batch.batch_time_end) batch ON batch.batch_no = crawl.batch_no " +
        "       INNER JOIN bcc_basic_info bcc ON bcc.bcc_id = crawl.bcc_id " +
        "WHERE ABS(TIMESTAMPDIFF(MINUTE, price_timestamp, :selectedDate)) <= :minuteUnit")
    List<BccPriceInfoCrawl> findNewestCrawl(@Param("selectedDate") String selectedDate, @Param("minuteUnit") int minuteUnit);
}
